1 Imports System.Data.SqlClient
2 Public Class frmClassFeeInfo
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(C_ID) FROM CourseFee")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Sub fillClass()
26 Try
27 con = New SqlConnection(cs)
28 con.Open()
29 adp = New SqlDataAdapter()
30 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ClassName) FROM Class", con)
31 ds = New DataSet("ds")
32 adp.Fill(ds)
33 dtable = ds.Tables(0)
34 cmbClass.Items.Clear()
35 For Each drow As DataRow In dtable.Rows
36 cmbClass.Items.Add(drow(0).ToString())
37 Next
38 Catch ex As Exception
39 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
40 End Try
41 End Sub
42 Sub fillFeeName()
43 Try
44 con = New SqlConnection(cs)
45 con.Open()
46 adp = New SqlDataAdapter()
47 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(FeeName) FROM Fee", con)
48 ds = New DataSet("ds")
49 adp.Fill(ds)
50 dtable = ds.Tables(0)
51 cmbFeeName.Items.Clear()
52 For Each drow As DataRow In dtable.Rows
53 cmbFeeName.Items.Add(drow(0).ToString())
54 Next
55 Catch ex As Exception
56 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
57 End Try
58 End Sub
59 Sub Reset()
60 cmbClass.SelectedIndex = -1
61 cmbFeeName.SelectedIndex = -1
62 cmbSemester.SelectedIndex = -1
63 cmbSchoolName.SelectedIndex = -1
64 txtFee.Text = ""
65 txtSearchByClass.Text = ""
66 txtSearchByFeeName.Text = ""
67 cmbClass.Focus()
68 btnSave.Enabled = True
69 btnUpdate.Enabled = False
70 btnDelete.Enabled = False
71 Getdata()
72 auto()
73 End Sub
74 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
75 Me.Close()
76 End Sub
77
78 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
79
80 If Len(Trim(cmbClass.Text)) = 0 Then
81 MessageBox.Show("Please select Class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
82 cmbClass.Focus()
83 Exit Sub
84 End If
85 If Len(Trim(cmbFeeName.Text)) = 0 Then
86 MessageBox.Show("Please select Fee Name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
87 cmbFeeName.Focus()
88 Exit Sub
89 End If
90 If Len(Trim(cmbSchoolName.Text)) = 0 Then
91 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
92 cmbSchoolName.Focus()
93 Exit Sub
94 End If
95 If Len(Trim(cmbSemester.Text)) = 0 Then
96 MessageBox.Show("Please select Semester", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
97 cmbSemester.Focus()
98 Exit Sub
99 End If
100 If Len(Trim(txtFee.Text)) = 0 Then
101 MessageBox.Show("Please enter fee", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
102 txtFee.Focus()
103 Exit Sub
104 End If
105 Try
106 con = New SqlConnection(cs)
107 con.Open()
108 Dim ct As String = "select CourseFee.Class,Feename,Semester,SchoolID from CourseFee where Class=@d1 and Feename=@d2 and Semester=@d3 and SchoolID=" & txtSchoolID.Text & ""
109 cmd = New SqlCommand(ct)
110 cmd.Connection = con
111 cmd.Parameters.AddWithValue("@d1", cmbClass.Text)
112 cmd.Parameters.AddWithValue("@d2", cmbFeeName.Text)
113 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
114 rdr = cmd.ExecuteReader()
115 If rdr.Read() Then
116 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
117 If (rdr IsNot Nothing) Then
118 rdr.Close()
119 End If
120 Return
121 End If
122 con = New SqlConnection(cs)
123 con.Open()
124 Dim cb As String = "insert into CourseFee(C_ID,Class,Feename,Semester,Fee,SchoolID) VALUES (" & txtID.Text & ",@d1,@d2,@d3,@d4," & txtSchoolID.Text & ")"
125 cmd = New SqlCommand(cb)
126 cmd.Connection = con
127 cmd.Parameters.AddWithValue("@d1", cmbClass.Text)
128 cmd.Parameters.AddWithValue("@d2", cmbFeeName.Text)
129 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
130 cmd.Parameters.AddWithValue("@d4", txtFee.Text)
131 cmd.ExecuteReader()
132 con.Close()
133 LogFunc(lblUser.Text, "added the new Course Fee having Fee Name '" & cmbFeeName.Text & "' for Class '" & cmbClass.Text & "' and Semester='" & cmbSemester.Text & "' and School '" & cmbSchoolName.Text & "'")
134 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
135 Getdata()
136 auto()
137 Catch ex As Exception
138 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
139 End Try
140 End Sub
141
142 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
143 Try
144 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
145 DeleteRecord()
146 End If
147 Catch ex As Exception
148 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
149 End Try
150 End Sub
151 Private Sub DeleteRecord()
152
153 Try
154
155 Dim RowsAffected As Integer = 0
156 con = New SqlConnection(cs)
157 con.Open()
158 Dim cq As String = "delete from CourseFee where C_ID=@d1"
159 cmd = New SqlCommand(cq)
160 cmd.Connection = con
161 cmd.Parameters.AddWithValue("@d1", txtID.Text)
162 RowsAffected = cmd.ExecuteNonQuery()
163 If RowsAffected > 0 Then
164 LogFunc(lblUser.Text, "deleted the Course Fee having Fee Name '" & cmbFeeName.Text & "' for Class '" & cmbClass.Text & "' and Semester='" & cmbSemester.Text & "' and School '" & cmbSchoolName.Text & "'")
165 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
166 Getdata()
167 Reset()
168 Else
169 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
170 Reset()
171 End If
172 If con.State = ConnectionState.Open Then
173 con.Close()
174
175 End If
176 Catch ex As Exception
177 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
178 End Try
179 End Sub
180
181 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
182 If Len(Trim(cmbClass.Text)) = 0 Then
183 MessageBox.Show("Please select Class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
184 cmbClass.Focus()
185 Exit Sub
186 End If
187 If Len(Trim(cmbFeeName.Text)) = 0 Then
188 MessageBox.Show("Please select Fee Name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
189 cmbFeeName.Focus()
190 Exit Sub
191 End If
192 If Len(Trim(cmbSchoolName.Text)) = 0 Then
193 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
194 cmbSchoolName.Focus()
195 Exit Sub
196 End If
197 If Len(Trim(cmbSemester.Text)) = 0 Then
198 MessageBox.Show("Please select Semester", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
199 cmbSemester.Focus()
200 Exit Sub
201 End If
202 If Len(Trim(txtFee.Text)) = 0 Then
203 MessageBox.Show("Please enter fee", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
204 txtFee.Focus()
205 Exit Sub
206 End If
207 Try
208 con = New SqlConnection(cs)
209 con.Open()
210 Dim cb As String = "Update CourseFee set Class=@d1,Feename=@d2,Semester=@d3,Fee=@d4,SchoolID=" & txtSchoolID.Text & " where C_ID=" & txtID.Text & ""
211 cmd = New SqlCommand(cb)
212 cmd.Connection = con
213 cmd.Parameters.AddWithValue("@d1", cmbClass.Text)
214 cmd.Parameters.AddWithValue("@d2", cmbFeeName.Text)
215 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
216 cmd.Parameters.AddWithValue("@d4", txtFee.Text)
217 cmd.ExecuteReader()
218 con.Close()
219 LogFunc(lblUser.Text, "updated the Course Fee having Fee Name '" & cmbFeeName.Text & "' for Class '" & cmbClass.Text & "' and Semester='" & cmbSemester.Text & "' and School '" & cmbSchoolName.Text & "'")
220 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
221 btnUpdate.Enabled = False
222 Getdata()
223 Catch ex As Exception
224 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
225 End Try
226 End Sub
227 Public Sub Getdata()
228 Try
229 con = New SqlConnection(cs)
230 con.Open()
231 cmd = New SqlCommand("SELECT RTRIM(C_ID), RTRIM(CourseFee.Class), RTRIM(Feename),RTRIM(SchoolID),RTRIM(SchoolName),RTRIM(Semester),RTRIM(Fee) from CourseFee,SchoolInfo where SchoolInfo.S_ID=CourseFee.SchoolID order by CourseFee.Class,FeeName", con)
232 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
233 dgw.Rows.Clear()
234 While (rdr.Read() = True)
235 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
236 End While
237 con.Close()
238 Catch ex As Exception
239 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
240 End Try
241 End Sub
242 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
243 Reset()
244 End Sub
245
246
247 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
248 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
249 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
250 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
251 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
252 End If
253 Dim b As Brush = SystemBrushes.ControlText
254 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
255
256 End Sub
257
258 Private Sub frmCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
259 Getdata()
260 fillClass()
261 fillFeeName()
262 fillSchool()
263 End Sub
264
265 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
266 Try
267 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
268 cmbClass.Text = dr.Cells(1).Value.ToString()
269 txtID.Text = dr.Cells(0).Value.ToString()
270 cmbFeeName.Text = dr.Cells(2).Value.ToString()
271 txtSchoolID.Text = dr.Cells(3).Value.ToString()
272 cmbSchoolName.Text = dr.Cells(4).Value.ToString()
273 cmbSemester.Text = dr.Cells(5).Value.ToString()
274 txtFee.Text = dr.Cells(6).Value.ToString()
275 btnUpdate.Enabled = True
276 btnDelete.Enabled = True
277 btnSave.Enabled = False
278 Catch ex As Exception
279 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
280 End Try
281 End Sub
282
283 Private Sub txtSearchByClass_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByClass.TextChanged
284 Try
285 con = New SqlConnection(cs)
286 con.Open()
287 cmd = New SqlCommand("SELECT RTRIM(C_ID), RTRIM(CourseFee.Class), RTRIM(Feename),RTRIM(SchoolID),RTRIM(SchoolName),RTRIM(Semester),RTRIM(Fee) from CourseFee,SchoolInfo where SchoolInfo.S_ID=CourseFee.SchoolID and CourseFee.Class like '" & txtSearchByClass.Text & "%' order by CourseFee.Class,FeeName", con)
288 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
289 dgw.Rows.Clear()
290 While (rdr.Read() = True)
291 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
292 End While
293 con.Close()
294 Catch ex As Exception
295 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
296 End Try
297 End Sub
298
299 Private Sub txtSearchByFeeName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByFeeName.TextChanged
300 Try
301 con = New SqlConnection(cs)
302 con.Open()
303 cmd = New SqlCommand("SELECT RTRIM(C_ID), RTRIM(CourseFee.Class), RTRIM(Feename),RTRIM(SchoolID),RTRIM(SchoolName),RTRIM(Semester),RTRIM(Fee) from CourseFee,SchoolInfo where SchoolInfo.S_ID=CourseFee.SchoolID and CourseFee.FeeName like '" & txtSearchByFeeName.Text & "%' order by CourseFee.Class,FeeName", con)
304 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
305 dgw.Rows.Clear()
306 While (rdr.Read() = True)
307 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
308 End While
309 con.Close()
310 Catch ex As Exception
311 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
312 End Try
313 End Sub
314
315 Private Sub txtFee_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtFee.KeyPress
316 Dim keyChar = e.KeyChar
317
318 If Char.IsControl(keyChar) Then
319 'Allow all control characters.
320 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
321 Dim text = Me.txtFee.Text
322 Dim selectionStart = Me.txtFee.SelectionStart
323 Dim selectionLength = Me.txtFee.SelectionLength
324
325 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
326
327 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
328 'Reject an integer that is longer than 16 digits.
329 e.Handled = True
330 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
331 'Reject a real number with two many decimal places.
332 e.Handled = False
333 End If
334 Else
335 'Reject all other characters.
336 e.Handled = True
337 End If
338 End Sub
339
340 Private Sub cmbSchoolName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSchoolName.SelectedIndexChanged
341 Try
342 con = New SqlConnection(cs)
343 con.Open()
344 cmd = con.CreateCommand()
345 cmd.CommandText = "SELECT S_ID FROM SchoolInfo where SchoolName=@d1"
346 cmd.Parameters.AddWithValue("@d1", cmbSchoolName.Text)
347 rdr = cmd.ExecuteReader()
348 If rdr.Read() Then
349 txtSchoolID.Text = rdr.GetValue(0)
350 End If
351 If (rdr IsNot Nothing) Then
352 rdr.Close()
353 End If
354 If con.State = ConnectionState.Open Then
355 con.Close()
356 End If
357 Catch ex As Exception
358 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
359 End Try
360 End Sub
361 Sub fillSchool()
362 Try
363 con = New SqlConnection(cs)
364 con.Open()
365 adp = New SqlDataAdapter()
366 adp.SelectCommand = New SqlCommand("SELECT distinct (SchoolName) FROM SchoolInfo", con)
367 ds = New DataSet("ds")
368 adp.Fill(ds)
369 dtable = ds.Tables(0)
370 cmbSchoolName.Items.Clear()
371 For Each drow As DataRow In dtable.Rows
372 cmbSchoolName.Items.Add(drow(0).ToString())
373 Next
374 Catch ex As Exception
375 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
376 End Try
377 End Sub
378 End Class
